package com.ying.dao.order;


import com.ying.po.order.Seller;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Optional;

public interface SellerRepository extends JpaRepository<Seller, Integer> {

    @Query(value = "select * from t_seller where is_delete=0 and user_id=:userId", nativeQuery = true)
    public Page<Seller> getSellerPageIsNotDeleted(Pageable pageable, @Param("userId") Integer userId);

    @Query(value = "select * from t_seller where is_delete=0 and user_id=?1", nativeQuery = true)
    public List<Seller> findAllIsNotDelete(Integer userId);

    @Query(value = "select * from t_seller where is_delete=0 and id=?1", nativeQuery = true)
    public Optional<Seller> findByIdAndIsNotDelete(Integer id);

    @Query(value = "select * from t_seller where is_delete=0 and user_id=:u and name like %:n%", nativeQuery = true)
    Page<Seller> getPageByName(Pageable pageable, @Param("n") String n, @Param("u") Integer u);

    @Query(value = "select * from t_seller where is_delete=0 and province=:p and user_id=:u", nativeQuery = true)
    Page<Seller> getPageByProvince(Pageable pageable, @Param("p")String p, @Param("u") Integer u);

    @Query(value = "select * from t_seller where is_delete=0 and user_id=:u and phone like :m%", nativeQuery = true)
    Page<Seller> getPageByPhone(Pageable pageable, @Param("m") String m, @Param("u") Integer u);

    @Query(value = "select * from t_seller where is_delete=0 and user_id=:u and name like %:n% and province=:p", nativeQuery = true)
    Page<Seller> getPageByNameAndProvince(Pageable pageable, @Param("n")String n, @Param("p")String p, @Param("u") Integer u);

    @Query(value = "select * from t_seller where is_delete=0 and name like %:n%  and user_id=:u and phone like :m%", nativeQuery = true)
    Page<Seller> getPageByNameAndPhone(Pageable pageable, @Param("n")String n, @Param("m")String m, @Param("u") Integer u);

    @Query(value = "select * from t_seller where is_delete=0 and province=:p and user_id=:u and phone like :m%", nativeQuery = true)
    Page<Seller> getPageByProvinceAndPhone(Pageable pageable, @Param("p")String p, @Param("m")String m, @Param("u") Integer u);

    @Query(value = "select * from t_seller where is_delete=0 and user_id=:u and name like %:n% and province=:p and phone like :m%", nativeQuery = true)
    Page<Seller> getPageByNameAndProvinceAndPhone(Pageable pageable, @Param("n")String n, @Param("p")String p, @Param("m")String m, @Param("u") Integer u);

    @Query(value = "select count(id) from t_seller where is_delete=0 and user_id=:u and type=0", nativeQuery = true)
    Integer getBuyerCount(@Param("u") Integer userId);

    @Query(value = "select id from t_seller where is_delete=0 and user_id=:u and type=0", nativeQuery = true)
    List<Integer> findBuyerIds(@Param("u") Integer userId);

    @Query(value = "select * from t_seller where is_delete=0 and user_id=:u and type=0 and year(create_time)=:y", nativeQuery = true)
    List<Seller> findNewBuyerThisYear(@Param("u")Integer userId, @Param("y")Integer year);
}
